<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class MY_Model extends CI_Model {

    public $table_name;
    private $user_info = FALSE;

    public function __construct() {
        parent::__construct();
        $this->position_list = array(
            'slides' => $this->lang->line('slides'),
            'middle' => $this->lang->line('middle'),
            'top' => $this->lang->line('top'),
            'left' => $this->lang->line('left'),
            'popup_middle' => $this->lang->line('popup_middle'),
            'top_left' => $this->lang->line('top_left'),
            'right' => $this->lang->line('right'),
            'top_right' => $this->lang->line('top_right'),
            'bottom_right' => $this->lang->line('bottom_right'),
            'bottom' => $this->lang->line('bottom'),
            'popup' => $this->lang->line('popup'),
        );
        $this->url_target_list = array(
            'normal' => $this->lang->line('url_target_normal'),
            '_blank' => $this->lang->line('url_target_blank'),
            '_top' => $this->lang->line('url_target_top'),
            'popup' => $this->lang->line('url_target_popup')
        );

        $this->active_list = array(
            '1' => $this->lang->line('active'),
            '0' => $this->lang->line('deactive')
        );

        $this->booking_types = array(
            '1' => $this->lang->line('vemoi'),
            '2' => $this->lang->line('xuatdoi')
        );
        $this->api_airlines_sale = array(
            '1' => 'VJ',
            '2' => 'VN'
        );
    }

    public function api_airlines_sale() {
        return $this->api_airlines_sale;
    }

    /**
     * Get status list.
     *
     * @return array
     */
    public function get_active_list() {
        return $this->active_list;
    }

    /**
     * Get value by key.
     *
     * @return string
     */
    public function get_value_by_key($key, $list) {
        return isset($list[$key]) ? $list[$key] : "";
    }

    /**
     * Get url_target list.
     *
     * @return array
     */
    public function get_url_target_list() {
        return $this->url_target_list;
    }

    /**
     * Get position_list name by position_list id.
     *
     * @return string
     */
    public function get_position_list() {
        return $this->position_list;
    }

    /**
     * Fetch all records.
     *
     * @return type
     */
    public function fetch_all() {
        $query = $this->db->get($this->table_name);
        return $query->result();
    }

    /**
     * Paginate results.
     *
     * @param type $offset
     * @param type $limit
     * @return type
     */
    public function paginate($limit = 10, $offset = 0) {
        $data = array();
        $this->db->limit($limit, $offset);
        $query = $this->db->get($this->table_name);

        return $query->result();
    }

    /**
     * Find record by id.
     *
     * @param type $id
     * @return type
     */
    public function find_by($where = FALSE, $select = "*", $is_single_result = FALSE, $order_by = NULL) {
        $this->db->select($select);
        if ($order_by != NULL) {
            $query = $this->db->order_by($order_by['key'], $order_by['value']);
        }
        if ($where != FALSE) {
            $where_values = array_values($where);
            $where_key = array_keys($where);
            if (is_array($where_values[0])) {
                $this->db->where_in($where_key[0], $where_values[0]);
                $query = $this->db->get($this->table_name);
            } else {
                $query = $this->db->get_where($this->table_name, $where);
            }
        } else {
            $query = $this->db->get($this->table_name);
        }

        return $is_single_result ? $query->row() : $query->result();
    }

    public function count_by($condition = FALSE) {
        if ($condition) {
            $this->db->where($condition);
        }

        $this->db->from($this->table_name);
        return $this->db->count_all_results();
    }

    /**
     * Abstract record creation.
     *
     * @param array $data
     * @return type
     */
    public function create($data) {
        $this->db->insert($this->table_name, $data);
        return $this->db->insert_id();
    }

    /**
     * Abstract recort update.
     *
     * @param array $data
     * @param type $id
     */
    public function update($data, $id, $field = null) {
        if ($field) {
            if (isset($field['customerID'])) {
                $this->db->update($this->table_name, $data, array('customerID' => $field['customerID']));
            }
            if (isset($field['owner']) && isset($field['lastupdated'])) {//controller sale//statitic
                $this->db->update($this->table_name, $data, array('owner' => $field['owner'], 'lastupdated' => $field['lastupdated']));
            }
        } else {
            $this->db->update($this->table_name, $data, array('id' => $id));
        }
    }

    /**
     * Abstract record deletion.
     *
     * @param type $id
     */
    public function delete($data) {
        switch ($this->table_name) {
            case 'book_contacts':
                $query = $this->db->query("UPDATE {$this->table_name} SET `active` = 0 WHERE `customerID` = '{$data['id']}'");
                $logAction = 'Xóa thành công KH, mã : ' . $data['id'];
                break;
            case 'users':
                $query = $this->db->query("UPDATE {$this->table_name} SET `active` = 0 WHERE `id` = '{$data['id']}'");
                $logAction = 'Xóa thành công user , id = ' . $data['id'];
                break;
            case 'groups':
                $query = $this->db->query("UPDATE {$this->table_name} SET `active` = 0 WHERE `id` = '{$data['id']}'");
                $logAction = 'Xóa nhóm thành công id = ' . $data['id'];
                break;
            case 'role':
                $query = $this->db->query("UPDATE {$this->table_name} SET `active` = 0 WHERE `id` = '{$data['id']}'");
                $logAction = 'Xóa phân quyền thành công id = ' . $data['id'];
                break;
            case 'book_contacts_updated':
                $query = $this->db->query("UPDATE {$this->table_name} SET `active` = 0 WHERE `customerID` = '{$data['id']}'");
                $logAction = 'Xóa thành công KH, mã : ' . $data['id'];
                break;
            default :
                $logAction = "";
                $this->db->delete($this->table_name, $data);
                break;
        }

        if ($logAction) {
            $paramAdminLog = array(
                'userid' => $this->session->userdata['user_id'],
                'lastLogin' => date('Y-m-d :H:i:s', time()),
                'ip' => $_SERVER['REMOTE_ADDR'],
                'logAction' => $logAction,
                'agent_code' => $this->session->userdata['agent_code']
            );
            $this->user_model->insertUserAdminLog($paramAdminLog);
        }
    }

    /**
     * Utiltiy method to create a UUID.
     *
     * @return type
     */
    protected function create_uuid() {
        $uuid_query = $this->db->query('SELECT UUID()');
        $uuid_rs = $uuid_query->result_array();
        return $uuid_rs[0]['UUID()'];
    }

    public function count() {
        return $this->db->count_all($this->table_name);
    }

    public function get_for_dropdownlist($id, $field) {
        $data = array();
        $query = $this->db->get($this->table_name);

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $data[$row->$id] = $row->$field;
            }
        }

        return $data;
    }

    /**
     * check code exists and rebuild code
     * return string
     */
    public function extract_code(&$code, $orginal_code) {
        $obj_query = $this->find_by(array('code' => $orginal_code), "id");
        if (isset($obj_query[0])) {
            $this->extract_code($code, $orginal_code . $this->lang->line('code_suffix'));
        } else {
            $code = $orginal_code;
        }
    }

    public function last_id() {
        return $this->db->query("SELECT LAST_INSERT_ID() AS ID")->row();
    }

    public function has_right_code($user_id, $role_code, $permission_code) {
        $role_list = $this->get_role_list($user_id);
        if (isset($role_list['code'][$role_code]) && in_array($permission_code, $role_list['code'][$role_code])) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function get_user_info($user_id) {
        if ($this->user_info === FALSE) {
            $this->db->select('users.username, users.group_id, groupright.role_id, groupright.permission_id, role.code as role_code, permission.code as permission_code');
            $this->db->from('users');
            $this->db->join('groups', 'users.group_id = groups.id');
            $this->db->join('groupright', 'groups.id = groupright.group_id');
            $this->db->join('role', 'role.id = groupright.role_id');
            $this->db->join('permission', 'permission.id = groupright.permission_id');
            $this->db->where('users.id', $user_id);
            $query = $this->db->get();

            $role_list = array();
            $user_info = FALSE;
            foreach ($query->result() as $item) {
                $user_info['role_list']['code'][$item->role_code][] = $item->permission_code;
                $user_info['role_list']['id'][$item->role_id][] = $item->permission_id;
                $user_info['username'] = $item->username;
                $user_info['group_id'] = $item->group_id;
            }
            $this->set_user_info($user_info);
            return $user_info;
        } else {
            return $this->user_info;
        }
    }

    public function get_group_id($user_id = FALSE) {
        if ($user_id === FALSE) {
            $user_id = $this->get_user_id();
        }
        $user_info = $this->get_user_info($user_id);
        return $user_info['group_id'];
    }

    public function set_user_info($user_info) {
        return $this->user_info = $user_info;
    }

    public function get_role_list($user_id) {
        if (!isset($this->user_info['role_list'])) {
            $user_info = $this->get_user_info($user_id);
            return $user_info['role_list'];
        }
        return $this->user_info['role_list'];
    }

    /**
     * Get user session data.
     *
     * @return type
     */
    public function get_user_data() {
        return $this->session->all_userdata();
    }

    /**
     * Get logged in user id.
     *
     * @return type
     */
    public function get_user_id() {
        $session_data = $this->session->all_userdata();
        return $session_data['user_id'];
    }

    /**
     * Get logged in username.
     *
     * @return string
     */
    public function get_username($user_id = FALSE) {

        if ($user_id === FALSE) {
            $user_id = $this->get_user_id();
        }

        if (!isset($this->user_info['username'])) {
            $user_info = $this->get_user_info($user_id);
            if (isset($user_info['username'])) {
                return $user_info['username'];
            }
            return '';
        } else {
            return $this->user_info['username'];
        }
    }

    private function stdclass_to_array($stdclass) {
        $array = array();
        if (is_array($stdclass)) {
            return $stdclass;
        }
        if (is_object($stdclass)) {
            foreach ($stdclass as $k => $item) {
                $array[$k] = $item;
            }
        }
        return $array;
    }

    private function box($controller, $id) {
        switch ($controller) {
            case 'refundTicket':
                $button .= '<li><a href="' . $controller . '/detail?id=' . $id . '"><i class="icon-pencil"></i> ' . $this->lang->line('view') . '</a> </li>';
                break;
        }
        return $button;
    }

    protected function add_button_box($controller, $right, $item) {
        $button = '';
        if ($right['add'] == TRUE || $right['delete'] == TRUE) {
            $button = '<div class="btn-group">';
            $button .= '<a href="#" data-toggle="dropdown" class="btn dropdown-toggle">';
            $button .= $this->lang->line('action');
            $button .= '<span class="caret"></span>';
            $button .= '</a>';
            $button .= '<ul class="dropdown-menu">';
            if ($right['edit'] == TRUE) {
                $button .= '<li><a href="' . site_url('auth/' . $controller . '/index/edit/' . $item['id']) . '"><i class="icon-pencil"></i> ' . $this->lang->line('edit') . '</a> </li>';
            }
            if ($right['delete'] == TRUE) {
                $button .= '<li><a data-toggle="modal" href="#delete_confirm" onclick="delete_confirm(\'' . site_url('auth/' . $controller . '/index/delete/' . $item['id']) . '\')"><i class="icon-remove"></i> ' . $this->lang->line('delete') . '</a></li>';
            }
            $button .= $this->box($controller, $item['id']);
            $button .= '</ul>';
            $button .= '</div>';
        }
        return $button;
    }

    protected function add_button($controller, $right, $item) {
        $button = '';
        if ($right['add'] == TRUE || $right['delete'] == TRUE) {
            $button = '<div class="btn-group">';
            $button .= '<a href="#" data-toggle="dropdown" class="btn dropdown-toggle">';
            $button .= $this->lang->line('action');
            $button .= '<span class="caret"></span>';
            $button .= '</a>';
            $button .= '<ul class="dropdown-menu">';
            if ($right['edit'] == TRUE) {
                $button .= '<li><a href="' . site_url('auth/' . $controller . '/index/edit/' . $item['id']) . '"><i class="icon-pencil"></i> ' . $this->lang->line('edit') . '</a> </li>';
            }
            if ($right['delete'] == TRUE) {
                $button .= '<li><a data-toggle="modal" href="#delete_confirm" onclick="delete_confirm(\'' . site_url('auth/' . $controller . '/index/delete/' . $item['id']) . '\')"><i class="icon-remove"></i> ' . $this->lang->line('delete') . '</a></li>';
            }
            $button .= '</ul>';
            $button .= '</div>';
        }
        return $button;
    }

    protected function add_button_customers($controller, $right, $item) {
        $button = '';
        if ($right['add'] == TRUE || $right['delete'] == TRUE) {
            $button = '<div class="btn-group">';
            $button .= '<a href="#" data-toggle="dropdown" class="btn dropdown-toggle">';
            $button .= $this->lang->line('action');
            $button .= '<span class="caret"></span>';
            $button .= '</a>';
            $button .= '<ul class="dropdown-menu">';
            if ($right['edit'] == TRUE) {
                $button .= '<li><a href="' . site_url('auth/' . $controller . '/index/edit/' . $item['customerID']) . '"><i class="icon-pencil"></i> ' . $this->lang->line('edit') . '</a> </li>';
            }
            if ($right['delete'] == TRUE) {
                $button .= '<li><a data-toggle="modal" href="#delete_confirm" onclick="delete_confirm(\'' . site_url('auth/' . $controller . '/index/delete/' . $item['customerID']) . '?do=list\')"><i class="icon-remove"></i> ' . $this->lang->line('delete') . '</a></li>';
            }
            //xem chi tiet
            $button .= '<li><a href="' . ($controller . '/detailCustomers?customerID=' . $item['customerID']) . '"><i class="icon-pencil"></i> ' . $this->lang->line('view') . '</a> </li>';
            $button .= '</ul>';
            $button .= '</div>';
        }
        return $button;
    }

    protected function add_button_status($controller, $right, $item) {
        $button = '';
        if ($right['add'] == TRUE || $right['delete'] == TRUE) {
            if ($right['edit'] == TRUE) {
                $button .= '<a href="' . site_url('auth/' . $controller . '/index/edit/' . $item) . '" title="' . $this->lang->line('edit') . '"><i class="icon-pencil"></i></a>';
            }
            $button .= '<a href="' . '/auth/' . $controller . '/updateBill?id=' . $item . '" class="fancy-sl" title ="' . $this->lang->line('quickprint') . '"><i class="icon-print"></i></a>';
            $button .= '<a href="' . '/auth/' . $controller . '/receipt?id=' . $item . '" class="fancy-sl" title ="' . $this->lang->line('printBill') . '"><i class="icon-print"></i></a>';
            $button .= '<a href="' . '/auth/' . $controller . '/detailCustomers?id=' . $item . '" title ="' . $this->lang->line('view') . '"><i class="icon-check"></i></a>';
            if ($right['delete'] == TRUE) {
                $button .= '<a href="/auth/' . $controller . '/delete?id=' . $item . '" class="fancy-sl">';
                $button .= '<i class="icon-remove"></i>';
                $button .= '</a>';
            }
            $button .= '<a href="/auth/' . $controller . '/sendsms?id=' . $item . '" class="fancy-sl">';
            $button .= '<img alt="Gởi tin nhắn SMS" title="Gởi tin nhắn SMS" src="' . STATIC_URL . 'backend/images/icon/sms.png">';
            $button .= '</a>';
            //time limit
            $button .= '<a href="/auth/' . $controller . '/timelimit?id=' . $item . '" class="fancy-sl">';
            $button .= '<i class="icon-time"></i>';
            $button .= '</a>';
        }
        return $button;
    }

    protected function add_button_payment($controller, $right, $item) {
        $button = '';
        if ($right['add'] == TRUE || $right['delete'] == TRUE) {
            if ($right['edit'] == TRUE) {
                $button .= '<a href="' . site_url('auth/' . $controller . '/index/edit/' . $item) . '" title="' . $this->lang->line('payment_confirm') . '"><i class="icon-pencil"></i></a>';
            }
            if ($right['delete'] == TRUE) {
                if ($controller == 'paymentAir') {
                    $button .= '<a href="/auth/bookingStatus/delete?session=air&id=' . $item . '" class="fancy-sl">';
                    $button .= '<i class="icon-remove"></i>';
                    $button .= '</a>';
                } else {
                    $button .= '<a data-toggle="modal" href="#delete_confirm" onclick="delete_confirm(\'' . site_url('auth/' . $controller . '/index/delete/' . $item) . '?do=list\')" title ="' . $this->lang->line('delete') . '"><i class="icon-remove"></i></a>';
                }
            }

            if ($controller == 'paymentAtHome') {
                $button .= '<a title="' . $this->lang->line('transportTicket') . '" class="fancy-sl" href="/auth/' . $controller . '/transportTicket?cid=' . $item . '"><i class="icon-home"></i></a>';
                $button .= '<a title="' . $this->lang->line('printBill') . '" class="fancy-sl" href="/auth/' . $controller . '/receipt?id=' . $item . '"><i class="icon-print"></i></a>';
            }
        }
        return $button;
    }

    protected function add_button_sales($controller, $right, $item) {
        $button = '';
        if ($right['add'] == TRUE || $right['delete'] == TRUE) {
            $button = '<div class="btn-group">';
            $button .= '<a href="#" data-toggle="dropdown" class="btn dropdown-toggle">';
            $button .= $this->lang->line('action');
            $button .= '<span class="caret"></span>';
            $button .= '</a>';
            $button .= '<ul class="dropdown-menu">';
            if ($right['edit'] == TRUE) {
                $button .= '<li><a href="' . site_url('auth/' . $controller . '/index/edit/' . $item['customerID']) . '"><i class="icon-pencil"></i> ' . $this->lang->line('edit') . '</a> </li>';
            }

            if ($right['delete'] == TRUE) {
                $button .= '<li><a data-toggle="modal" href="#delete_confirm" onclick="delete_confirm(\'' . site_url('auth/' . $controller . '/index/delete/' . $item['customerID']) . '?do=list\')"><i class="icon-remove"></i> ' . $this->lang->line('delete') . '</a></li>';
            }

            $button .= '</ul>';
            $button .= '</div>';
        }
        return $button;
    }

    protected function add_button_history($controller, $right, $item) {
        $button = '<a href="/auth/bookhistory/updateBill?id=' . $item . '" class="fancy-sl">';
        $button .= '<img alt="In" title="In" src="' . STATIC_URL . '/backend/images/icon/print.png">';
        $button .= '</a>';
        $button .= '<a href="/auth/bookhistory/exportBill?id=' . $item . '" class="fancy-sl">';
        $button .= '<img alt="Xuất hóa đơn" title="Xuất hóa đơn" src="' . STATIC_URL . 'backend/images/icon/export.png">';
        $button .= '</a>';
        $button .= '<a href="/auth/bookhistory/receipt?id=' . $item . '" class="fancy-sl">';
        $button .= '<img alt="In phiếu thu" title="In phiếu thu" src="' . STATIC_URL . 'backend/images/icon/phieuthu.png">';
        $button .= '</a>';
        $button .= '<a href="/auth/bookhistory/sendsms?id=' . $item . '" class="fancy-sl">';
        $button .= '<img alt="Gởi tin nhắn SMS" title="Gởi tin nhắn SMS" src="' . STATIC_URL . 'backend/images/icon/sms.png">';
        $button .= '</a>';
//                $button     .= '<a href="/auth/bookhistory/noteFly?id='.$item.'" class="fancy-sl">';
//                $button     .= '<i class="icon-pencil"></i>';
//                $button     .= '</a>';
        //DUYET BOOKING DA THANH TOAN
        if (in_array($this->session->userdata['group_id'], array(1, 76, 80))) {
            $button .= '<a href="/auth/bookhistory/checker?id=' . $item . '" class="fancy-sl">';
            $button .= '<img alt="Duyet booking" title="Duyet booking" src="' . STATIC_URL . 'backend/images/icon/status.png">';
            $button .= '</a>';
        }
        return $button;
    }

    protected function add_button_public($controller, $right, $item) {
        $button = '';
        if ($controller == 'flyTomorrow') {
            if ($item['status'] == 1) {
                $button = '<img src = "' . STATIC_URL . 'backend/images/icon/calling.gif" />';
            } else {
                $button = '<a class="fancy-sl" id="fancy-sl" adata="' . $item['customerID'] . '" href="' . '/auth/' . $controller . '/display?id=' . $item['id'] . '"><img src = "' . STATIC_URL . 'backend/images/icon/call.png" /></a>';
            }
        }

        if ($controller == "customerAssess") {
            if ($item['status'] == 1) {
                $button = '<img src = "' . STATIC_URL . 'backend/images/icon/calling.gif" />';
            } else {
                $button = '<a class="fancy-sl" id="fancy-sl" adata="' . $item['customerID'] . '" href="' . site_url('auth/' . $controller . '/index/edit/' . $item['customerID']) . '"><img src = "' . STATIC_URL . 'backend/images/icon/call.png" /></a>';
            }
        }

        if (in_array($controller, array("voucher", "voucherHistory", "voucherArise"))) {
            if ($item['net_fare'] == 0) {
                $button .= '<a title="' . $this->lang->line('view') . '" alt="' . $this->lang->line('view') . '" href="/auth/refundTicket/view?id=' . $item['refundticket_id'] . '" class="fancy-sl"><i class="icon-screenshot"></i></a>';
            } else {

                if ($this->session->userdata['user_id'] == 178) {
                    if ($item['approve'] == 0) {
                        $button .= '<a title="' . $this->lang->line('approve') . '" alt="' . $this->lang->line('approve') . '" href="/auth/' . $controller . '/approve?id=' . $item['id'] . '" class="fancy-sl"><i class="icon-ok-sign"></i></a>';
                    }
                } else {
                    if (in_array($this->session->userdata['group_id'], array(1, 79))) {

                        $button = '<a title="' . $this->lang->line('payment_confirm') . '" alt="' . $this->lang->line('payment_confirm') . '" class="fancy-sl" id="fancy-sl" href="' . '/auth/' . $controller . '/check?id=' . $item['id'] . '"><i class="icon-check"></i></a>';
                        $button .= '<a title="' . $this->lang->line('printBill') . '" alt="' . $this->lang->line('printBill') . '" href="/auth/' . $controller . '/receipt?id=' . $item['id'] . '" class="fancy-sl"><i class="icon-print"></i></a>';
                        $button .= '<a title="' . $this->lang->line('permission_ticker') . '" alt="' . $this->lang->line('permission_ticker') . '" href="/auth/' . $controller . '/transportTicket?id=' . $item['id'] . '" class="fancy-sl"><i class="icon-home"></i></a>';
                    }
                    if ($right['edit'] == TRUE || $right['delete'] == TRUE) {
                        if ($right['edit'] == TRUE) {
                            $button .= '<a title="' . $this->lang->line('edit') . '" alt="' . $this->lang->line('edit') . '" href="' . site_url('auth/' . $controller . '/index/edit/' . $item['id']) . '"><i class="icon-pencil"></i></a>';
                        }
                        if ($right['delete'] == TRUE) {
                            $button .= '<a href="/auth/voucher/delete?session=ct&id=' . $item['id'] . '" class="fancy-sl">';
                            $button .= '<i class="icon-remove"></i>';
                            $button .= '</a>';
                        }
                    }
                }
            }
        }

        if ($controller == 'refundTicket') {
            if ($right['add'] == TRUE || $right['delete'] == TRUE) {
                if ($right['edit'] == TRUE) {
                    $button .= '<a title="' . $this->lang->line('edit') . '" alt="' . $this->lang->line('edit') . '" href="' . site_url('auth/' . $controller . '/index/edit/' . $item['id']) . '"><i class="icon-pencil"></i></a>';
                }
                if ($right['delete'] == TRUE) {
                    $button .= '<a title="' . $this->lang->line('delete') . '" alt="' . $this->lang->line('delete') . '" data-toggle="modal" href="#delete_confirm" onclick="delete_confirm(\'' . site_url('auth/' . $controller . '/index/delete/' . $item['id']) . '?do=list\')"><i class="icon-remove"></i></a>';
                }
            }
            if (in_array($this->session->userdata['group_id'], array(1, 79))) {
                $button .= '<a alt="' . $this->lang->line('accept') . '" title="' . $this->lang->line('accept') . '" href="/auth/' . $controller . '/accept?id=' . $item['id'] . '" class="fancy-sl">';
                $button .= '<i class="icon-check"></i>';
                $button .= '</a>';
            }
        }
        
        if (in_array($controller, array("voucherLunisolar"))) {
                $button .= '<a alt="' . $this->lang->line('trade') . '" title="' . $this->lang->line('trade') . '" href="/auth/' . $controller . '/trade?id=' . $item['id'] . '" class="fancy-sl">';
                $button .= '<i class="icon-check"></i>';
                $button .= '</a>';
        }

        return $button;
    }

    public function provinceById($params) {
        $sql = "SELECT name FROM book_province AS pr WHERE pr.id = {$params}";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function getListProvince() {
        $sql = "SELECT * FROM book_province";
        $query = $this->db->query($sql);
        $province[''] = $this->lang->line('choose') . ' ' . $this->lang->line('province');
        $result = $query->result();
        foreach ($result as $keys => $values) {
            $province[$values->id] = $values->name;
        }
        return $province;
    }

    public function getListTypeCustomers() {
        $sql = "SELECT name FROM book_type_customers";
        $query = $this->db->query($sql);
        $type_customers[] = '';
        $result = $query->result();
        foreach ($result as $keys => $values) {
            $type_customers[$keys + 1] = $values->name;
        }
        return $type_customers;
    }

    public function getListPaymentMethod() {
        $sql = "SELECT * FROM book_payment_method";
        $query = $this->db->query($sql);
        $payment[''] = 'Vui lòng chọn';
        $result = $query->result();
        foreach ($result as $keys => $values) {
            $payment[$values->id] = $values->payment_name;
        }
        return $payment;
    }

    public function getListArea() {
        $sql = "SELECT * FROM book_area";
        $query = $this->db->query($sql);
        $area[] = '';
        $result = $query->result();
        foreach ($result as $keys => $values) {
            $area[$keys + 1] = $values->area_name;
        }
        return $area;
    }

    public function getListCallquality() {
        $sql = "SELECT * FROM book_callquality";
        $query = $this->db->query($sql);
        $callquality[] = '';
        $result = $query->result();
        foreach ($result as $keys => $values) {
            $callquality[$keys + 1] = $values->callquality_name;
        }
        return $callquality;
    }

    public function getListCommunication() {
        $sql = "SELECT * FROM book_communication";
        $query = $this->db->query($sql);
        $communication[] = '';
        $result = $query->result();
        foreach ($result as $keys => $values) {
            $communication[$keys + 1] = $values->communication_name;
        }
        return $communication;
    }

    public function getListInterested() {
        $sql = "SELECT * FROM book_interested";
        $query = $this->db->query($sql);
        $interested[] = '';
        $result = $query->result();
        foreach ($result as $keys => $values) {
            $interested[$keys + 1] = $values->interested_name;
        }
        return $interested;
    }

    public function getListAgentCode($params = null) {

        $this->db->select("id, pcc");
        $this->db->from('book_agent_code');
        $this->db->where('server_status', 1);
        if (!empty($params)) {
            $this->db->where('id', $params);
        }
        $query = $this->db->get();
        $result = $query->result();

        foreach ($result as $keys => $values) {
            $agent_code[$values->id] = $values->pcc;
        }
        return $agent_code;
    }

    public function getListBanks($params = null) {
        $this->db->select('*');
        $this->db->from('book_bank');
        if (isset($params) && $params) {
            $this->db->where('id', $params);
        }
        $query = $this->db->get();
        return $query->result();
    }

    public function getListDistrict($province = null) {
        $this->db->select('*');
        $this->db->from('book_district');
        if (isset($province) && $province) {
            $this->db->where('province_id', $province);
        }
        $query = $this->db->get();
        $district[''] = $this->lang->line('choose') . ' ' . $this->lang->line('district');
        foreach ($query->result() as $values) {
            $district[$values->id] = $values->name;
        }
        return $district;
    }

    public function selectDetailCustomers($params) {
        $this->db->select('c.customerID,c.name,c.address,c.phone,c.social,DATE_FORMAT(c.birthday,("%d-%m-%Y")) AS birthday,c.info_bill,c.note,c.lastupdated,c.lastBooking,c.totalMoneyBooking,c.surplus,c.salesID,t.name AS type,ag.pcc AS agent_code,prov.name AS province,usr.username AS username')
                ->from('book_contacts AS c')
                ->join('book_type_customers AS t', 't.id = c.type')
                ->join('book_agent_code AS ag', 'c.agent_code = ag.id')
                ->join('book_province AS prov', 'prov.id = c.province')
                ->join('users AS usr', 'usr.id = c.owner')
                ->where('c.active = 1')
                ->where('c.customerID = "' . $params . '"');

        $query = $this->db->get();
        return $query->result();
    }

    public function selectDetailCustomersNew($params) {
        $this->db->select('c.customerID,c.name,c.address,c.phone,c.email,c.note_customers,c.note,c.lastupdated,c.frequency,t.name AS type,ag.pcc AS agent_code,prov.name AS province,usr.username AS username,area.area_name,cq.callquality_name,coms.communication_name,irt.interested_name')
                ->from('book_contacts_updated AS c')
                ->join('book_type_customers AS t', 't.id = c.type')
                ->join('book_agent_code AS ag', 'c.agent_code = ag.id')
                ->join('book_province AS prov', 'prov.id = c.province')
                ->join('users AS usr', 'usr.id = c.owner')
                ->join('book_area AS area', 'area.id = c.area')
                ->join('book_callquality AS cq', 'cq.id = c.callquality')
                ->join('book_communication AS coms', 'coms.id = c.communication')
                ->join('book_interested AS irt', 'irt.id = c.interested')
                ->where('c.active = 1')
                ->where('c.customerID = "' . $params . '"');

        $query = $this->db->get();
        return $query->result();
    }

    public function getListUsers($id = NULL) {
        $sql = "SELECT * FROM users";

        if ($id) {
            $sql .= " where $id";
        }

        $query = $this->db->query($sql);
        $result = $query->result();

        $users[''] = 'Vui lòng chọn';
        foreach ($result as $keys => $values) {
            $users[$values->id] = $values->username;
        }
        return $users;
    }

    public function countTypeSales($params1, $params2, $params3) {
        $sql = "SELECT count(*) AS total FROM `book_sales` WHERE 1 and `lastupdated` > {$params2} AND type = {$params1} AND owner = {$params3}";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function getPaymentMethodById($params) {
        $sql = "SELECT payment_name FROM book_payment_method WHERE id = {$params}";
        $query = $this->db->query($sql);
        $result = $query->result();
        return $result[0];
    }

    public function getInfoUserByParams($params) {
        $sql = "SELECT * FROM users";
        if ($params) {
            $sql .= ' WHERE ' . $params['key'] . ' = ' . $params['value'];
        }
        $query = $this->db->query($sql);
        $result = $query->result();
        return $result[0];
    }

    public function getInfoSalersById($params) {
        $sql = "SELECT usr.username FROM book_contacts as cts JOIN users AS usr ON cts.salesID = usr.id WHERE cts.customerID = '{$params}'";
        $query = $this->db->query($sql);
        $result = $query->result();
        return ($result) ? $result[0] : '';
    }

    public function getInfoContactsByParams($params) {
        $sql = "SELECT * FROM book_contacts";
        if ($params) {
            $sql .= ' WHERE ' . $params['key'] . ' = "' . $params['value'] . '"';
        }

        $query = $this->db->query($sql);
        $result = $query->result();
        return $result[0];
    }

    public function getListTicketWorkers($params = null) {
        $this->db->select('id,name');
        $this->db->from('book_ticketworkers');
        $this->db->where('status', '1');
        if (isset($params) && $params) {
            $this->db->where('id', $params);
        }
        $query = $this->db->get();
        $result[''] = $this->lang->line('choose') . ' ' . $this->lang->line('staff');
        foreach ($query->result() as $values) {
            $result[$values->id] = $values->name;
        }
        return $result;
    }

    public function getInfoTicketByParams($booking_id = null) {

        $this->db->select("tbl.*");
        $this->db->from('book_ticket' . ' AS tbl');

        if ($booking_id) {
            $this->db->where('tbl.booking_id ', $booking_id);
        }

        $query = $this->db->get();
//                echo $this->db->last_query();exit;
        return $query->result();
    }

    public function getInfoBookingHold($booking_id = null, $airline = null, $booking_place_code = null, $customerID = null, $owner = null, $booking_types = null) {

        $this->db->select("tbl.*");
        $this->db->from('book_list_booking' . ' AS tbl');

        if ($booking_id) {
            $this->db->where('tbl.id', $booking_id);
        }

        if ($airline) {
            $this->db->where('tbl.airline', $airline);
        }

        if ($booking_place_code) {
            $this->db->where('tbl.booking_place_code', $booking_place_code);
        }

        if ($owner) {
            $this->db->where('tbl.owner', $owner);
        }

        if ($booking_types) {
            $this->db->where('tbl.booking_types', $booking_types);
        }

        if ($customerID) {
            $this->db->where('tbl.customerID', $customerID);
        }
        $this->db->order_by("lastupdated", "DESC");
        $query = $this->db->get();
//                echo $this->db->last_query();exit;
        return $query->result();
    }

    public function getInfoTable($params, $table) {

        $this->db->select("tbl.*");
        $this->db->from($table . ' AS tbl');

        if ($params['booking_id']) {
            $this->db->where('tbl.booking_id', $params['booking_id']);
        }

        if ($params['customerID']) {
            $this->db->where('tbl.customerID', $params['customerID']);
            $this->db->order_by("lastupdated", "DESC");
            $this->db->limit(1);
        }

        if ($params['voucher_id']) {
            $this->db->where('tbl.voucher_id', $params['voucher_id']);
        }

        if ($params['bankid']) {
            $this->db->where('tbl.id', $params['bankid']);
        }

        $query = $this->db->get();
//                echo $this->db->last_query();exit;
        return $query->result();
    }

    public function districtById($params) {
        $sql = "SELECT name FROM book_district AS pr WHERE pr.id = {$params}";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function getNameTicker($voucher_id = NULL) {
        $this->db->select("bt.*");
        $this->db->from('book_voucher_ticket AS bvt');
        $this->db->join('book_ticketworkers AS bt', 'bt.id = bvt.ticketWorkers');

        if ($voucher_id) {
            $this->db->where('bvt.voucher_id', $voucher_id);
        }

        $query = $this->db->get();
//                echo $this->db->last_query();exit;
        return $query->result();
    }

    public function selectVoucher($airline = NULL, $booking_place_code = NULL, $customerID = NULL, $owner = NULL) {
        $this->db->select("bv.*");
        $this->db->from('book_voucher AS bv');

        if ($airline) {
            $this->db->like('bv.airline', $airline);
        }

        if ($booking_place_code) {
            $this->db->like('bv.booking_place_code', $booking_place_code);
        }

        if ($customerID) {
            $this->db->where('bv.customerID', $customerID);
        }

        if ($owner) {
            $this->db->where('bv.owner', $owner);
        }

        $query = $this->db->get();
//                echo $this->db->last_query();exit;
        return $query->result();
    }

    public function getInfoUsers($params) {
        $this->db->select("bt.*");
        $this->db->from('book_voucher_ticket AS bvt');
        $this->db->join('book_ticketworkers AS bt', 'bt.id = bvt.ticketWorkers');

        if ($voucher_id) {
            $this->db->where('bvt.voucher_id', $voucher_id);
        }

        $query = $this->db->get();
//                echo $this->db->last_query();exit;
        return $query->result();
    }

    public function getOwnerFromTblUser($owner) {
        $this->db->select("firstname, lastname, phone");
        $this->db->from('users AS usr');
        if ($owner) {
            $this->db->where('usr.id', $owner);
        }

        $query = $this->db->get();
        return $query->result();
    }

    public function getListClause() {
        $this->db->select("*");
        $this->db->from('book_clause');
        $query = $this->db->get();
        return $query->result();
    }

}
